# -*- coding: utf-8 -*-
"""
author : heting
date : 2018/6/14 9:15
desc : 
"""
import hashlib
from MyComFiles.MyPublic import *
from MyComFiles.qiYuan.user import User
from MyComFiles.qiYuan.keyMap import keyMap

conn = pymysql.connect(host='172.16.1.90', port=3306, user='lianghua', passwd='xc123', db='qy_approval_prod',
                       charset='UTF8')
engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/qy_datasys?charset=utf8', echo=False)

os.chdir('./')
workPath = os.getcwd()
print('workPath: ', os.getcwd())

def gruopOperData(dataDf,type='date'):
    df = DataFrame()
    df['succ'] = dataDf['successUsers'].groupby(dataDf[type]).sum()
    df['fail'] = dataDf['failUsers'].groupby(dataDf[type]).sum()
    df['loan_amount'] = 0.000001*dataDf['approved_amount'].groupby(dataDf[type]).sum()
    df['total_amount'] = df['loan_amount'].cumsum()
    df['succHis'] = df['succ'].cumsum()
    df['per_amount'] = round(df['loan_amount']/df['succ'],2)
    df['per_his_amount'] = round(df['total_amount']/df['succHis'], 2)
    df['passRate'] = round(100*df['succ']/(df['succ']+df['fail']),2)
    df = df.fillna(0)
    return df


"""运营数据"""
def getOperData():
    dateList = newDateListAll('2017-05-30')
    dataDf = DataFrame()
    dataDf['date'] = dateList
    dataDf['week'] = dataDf['date'].map(lambda x: '%sw%s' % ((pd.to_datetime(x)).year, (pd.to_datetime(x)).week))
    dataDf['year'] = dataDf['date'].map(lambda x: x[:4])
    dataDf['month'] = dataDf['date'].map(lambda x: x[:7])
    sql = """
        --
        -- 放款成功的用户量+金额-每日
        SELECT
                date(t.loan_at) date,
                count(DISTINCT t.user_id_num) successUsers,
            sum(t.approved_amount) approved_amount
        FROM
                qy_approval_prod.approval_orders t
        WHERE
                t.loan_at IS NOT NULL
        AND t.operator_status = '放款成功'
        GROUP BY
                date(t.loan_at)
        ORDER BY
                date;
    """
    df = pdReadSqlNew(sql,conn)
    df['date'] =df['date'].map(lambda x:x.strftime('%Y-%m-%d'))
    dataDf = pd.merge(dataDf, df, on='date', how='left')
    dataDf = dataDf.fillna(0)

    sql = """
        --
        -- 查询放款失败的用户-每日
        SELECT
            date(t.approval_end_time) date,
            count(DISTINCT t.user_id_num) failUsers
        FROM
            qy_approval_prod.approval_orders t
        WHERE
            t.approval_end_time IS NOT NULL
        AND t.operator_status <> '放款成功'
        GROUP BY
            date(t.approval_end_time)
        ORDER BY
            date;
    """
    df = pdReadSqlNew(sql,conn)
    df['date'] = df['date'].map(lambda x:x.strftime('%Y-%m-%d'))
    dataDf = pd.merge(dataDf, df, on='date', how='left')
    dataDf = dataDf.fillna(0)

    data = [{'key':'year','value':'年','show':True},
            {'key':'month','value':'月','show':True},
            {'key':'week','value':'周','show':True},
            {'key': 'date','value':'日','show': True}]

    showList = []
    for item in data:
        if item['show']:
            showList.append(item['key'])

    mainPath = os.path.dirname(workPath)
    mainFileName = os.path.join(mainPath,'static/xlsx/operData')
    sysdate = ''
    writer = pd.ExcelWriter('%s%s.xlsx' % (mainFileName,sysdate), engine='xlsxwriter')
    
    sysdate = datetime.datetime.now().strftime("%Y%m%d")
    for item in data:
        df = gruopOperData(dataDf, type=item['key'])
        df['queryDate'] = sysdate
        indexCol = df.index.name
        df.to_sql('oper_%s'%item['key'],if_exists='replace',con=engine,dtype={'queryDate':String(20),indexCol:String(20),"succ": Integer(),"fail": Integer(),"succHis": Integer(),"loan_amount": FLOAT(),"total_amount": FLOAT(),"per_amount": FLOAT(),"per_his_amount": FLOAT(),"yearPassRate": FLOAT()})
        df.to_excel(writer, sheet_name='{}'.format(item['key']))
    writer.save()

    time_end = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    print('结束时间:%s'%time_end)

time_start = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
print('开始时间:', time_start)
getOperData()
conn.close()